# 使用python构建一个sqlite的工具类：
# 实现 创建（表格），和插入（数据）两个操作
import sqlite3
from typing import List, Dict, Optional, Any


class SQLiteTool:
    def __init__(self, db_name: str = "database.db"):
        """
        初始化SQLite工具类

        :param db_name: 数据库文件名，默认为'database.db'
        """
        self.db_name = db_name
        self.connection = None

    def connect(self) -> None:
        """连接到数据库"""
        self.connection = sqlite3.connect(self.db_name)

    def close(self) -> None:
        """关闭数据库连接"""
        if self.connection:
            self.connection.close()
            self.connection = None

    def create_table(self, table_name: str, columns: Dict[str, str]) -> bool:
        """
        创建表格

        :param table_name: 表名
        :param columns: 列名和类型的字典，例如 {'id': 'INTEGER PRIMARY KEY', 'name': 'TEXT'}
        :return: 创建是否成功
        """
        if not columns:
            return False

        try:
            self.connect()
            cursor = self.connection.cursor()

            # 构建列定义字符串
            columns_def = ", ".join([f"{name} {type_}" for name, type_ in columns.items()])

            # 执行创建表SQL
            sql = f"CREATE TABLE IF NOT EXISTS {table_name} ({columns_def})"
            cursor.execute(sql)
            self.connection.commit()
            return True
        except sqlite3.Error as e:
            print(f"创建表时出错: {e}")
            return False
        finally:
            self.close()

    def insert_data(self, table_name: str, data: Dict[str, Any]) -> bool:
        """
        插入数据

        :param table_name: 表名
        :param data: 要插入的数据字典，键为列名，值为数据
        :return: 插入是否成功
        """
        if not data:
            return False

        try:
            self.connect()
            cursor = self.connection.cursor()

            # 构建列名和占位符
            columns = ", ".join(data.keys())
            placeholders = ", ".join(["?"] * len(data))

            # 执行插入SQL
            sql = f"INSERT INTO {table_name} ({columns}) VALUES ({placeholders})"
            cursor.execute(sql, tuple(data.values()))
            self.connection.commit()
            return True
        except sqlite3.Error as e:
            print(f"插入数据时出错: {e}")
            return False
        finally:
            self.close()

    def __enter__(self):
        """支持上下文管理协议"""
        self.connect()
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        """支持上下文管理协议"""
        self.close()


# 示例用法
if __name__ == "__main__":
    # 创建工具实例
    db_tool = SQLiteTool("example.db")

    # 创建表
    table_name = "users"
    columns = {
        "id": "INTEGER PRIMARY KEY AUTOINCREMENT",
        "name": "TEXT NOT NULL",
        "age": "INTEGER",
        "email": "TEXT UNIQUE"
    }
    if db_tool.create_table(table_name, columns):
        print(f"表 '{table_name}' 创建成功")

    # 插入数据
    user_data = {
        "name": "张三",
        "age": 25,
        "email": "zhangsan@example.com"
    }
    if db_tool.insert_data(table_name, user_data):
        print("数据插入成功")

    # 使用上下文管理器方式
    with SQLiteTool("example.db") as tool:
        # 批量插入多条数据
        users = [
            {"name": "李四", "age": 30, "email": "lisi@example.com"},
            {"name": "王五", "age": 28, "email": "wangwu@example.com"},
            {"name": "赵六", "age": 35, "email": "zhaoliu@example.com"}
        ]
        for user in users:
            tool.insert_data(table_name, user)
        print("批量插入数据完成")
